package abc.Tool;

import java.io.IOException;
import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

/**
 * @author 王艳林
 * @email 981247127@qq.com
 * @time 2021-12-09 09:13:46
 **/
public class JdbcUtils {
	// 配置文件
	private static final String conf = "/db.properties";
	// 连接数据库的地址
	private static final String url;
	// 连接数据库的账号
	private static final String username;
	// 连接数据库的密码
	private static final String password;
	// 数据库连接
	private static Connection connection = null;

	// 静态初始化
	static {
		Properties properties = new Properties();
		try {
			Class.forName("com.mysql.jdbc.Driver");
			properties.load(JdbcUtils.class.getResourceAsStream(conf));
		} catch (ClassNotFoundException | IOException e) {
			e.printStackTrace();
		}
		// 尝试从配置文件中读取数据库的连接信息
		url = properties.getProperty("url", "jdbc:mysql://localhost:3306/mysql");
		username = properties.getProperty("username", "root");
		password = properties.getProperty("password", "root");
	}

	/**
	 * 获取数据库连接
	 */
	private static synchronized Connection getConnection() {
		try {
			// 如果没有连接数据库，或数据库连接被关闭，尝试连接
			if (connection == null || connection.isClosed()) {
				connection = DriverManager.getConnection(url, username, password);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return connection;
	}

	/**
	 * 读取结果集并转换为指定的对象
	 *
	 * @param set 结果集
	 * @param c   泛型类对象
	 * @param <T> 泛型类
	 * @return 返回一组类的实例
	 * @throws SQLException, InstantiationException, IllegalAccessException
	 */
	@SuppressWarnings("unchecked")
	private static <T> List<T> readSet(ResultSet set, Class<T> c) throws SQLException, InstantiationException, IllegalAccessException {
		// 通过结果集的 getMetaData 可以获取结果集的描述性信息
		ResultSetMetaData data = set.getMetaData();
		// 借助 ResultSetMetaData 得到所有需要赋值的字段的集合
		List<Field> fields = new ArrayList<>();
		// 1. 得到列的总数
		int count = 0;
		try {
			count = data.getColumnCount();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		// 2. 通过循环，得到每一列的名称和类型
		for (int i = 1; i <= count; i++) {
			// 3. 通过每一列的名称反射字段
			try {
				// 从描述信息中获取列名
				String name = data.getColumnName(i);
				// 从描述信息中获取列的类型
				// int columnType = data.getColumnType(i);
				// String typeName = data.getColumnTypeName(i);
				Field field = c.getDeclaredField(name);
				// 4. 将所有的字段组装为一个集合
				fields.add(field);
			} catch (SQLException | NoSuchFieldException | SecurityException e) {
				// e.printStackTrace();
			}
		}

		List<T> ts = new ArrayList<>();

		while (set.next()) {
			T t = null;
			// 处理基础数据
			if (c.isAssignableFrom(Integer.class)
					|| c.isAssignableFrom(Long.class)
					|| c.isAssignableFrom(Double.class)
					|| c.isAssignableFrom(String.class)) {
				t = (T) set.getObject(1);
			} else if (c.isAssignableFrom(Boolean.class)) {
				long v = set.getLong(1);
				Boolean val = v > 0;
				t = (T) val;
			} else {
				// 1. 反射实例
				t = c.newInstance();
				// 2. 为实例的属性赋值
				for (Field field : fields) {
					field.setAccessible(true);
					// 通过字段的名称获取其值，并赋值给实例 t
					field.set(t, set.getObject(field.getName()));
				}
			}
			ts.add(t);
		}
		set.close();
		return ts;
	}

	public static <T> List<T> selects(String sql, Class<T> c, Object... ps) {
		List<T> ts = new ArrayList<>();
		try {
			PreparedStatement statement = getConnection().prepareStatement(sql);
			if (ps != null && ps.length > 0) {
				for (int i = 0; i < ps.length; i++) {
					statement.setObject(i + 1, ps[i]);
				}
			}
			ResultSet set = statement.executeQuery();
			ts = readSet(set, c);
			// 关闭查询
			statement.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return ts;
	}

	public static <T> T select(String sql, Class<T> c, Object... ps) {
		List<T> ts = selects(sql, c, ps);
		if (ts != null && !ts.isEmpty()) {
			return ts.get(0);
		}
		return null;
	}


	public static int modify(String sql, Object... ps) {
		int r = 0;
		try {
			PreparedStatement statement = getConnection().prepareStatement(sql);
			if (ps != null && ps.length > 0) {
				for (int i = 0; i < ps.length; i++) {
					statement.setObject(i + 1, ps[i]);
				}
			}
			r = statement.executeUpdate();
			// 关闭查询
			statement.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return r;
	}
}
